<?php
namespace Home\Controller;
use Common\Common\Classes\Message;
use Home\Model\ClassService;
use Home\Model\ClassStudentService;
use Home\Model\QuestionSetService;
use Home\Model\StudentResultService;
use Home\Model\StudentService;
use Home\Model\TeacherService;
use Think\Controller;
use Think\Storage;

class ExportExcelController extends HomeController{
    //---------------------------------导出学生的所有成绩----------------------------------------------
    public function exportAllGrades(){
        vendor("PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $cla_sql = new ClassService();
        $check = $cla_sql->checkPower();
        if ($check->status == 1) {
            date_default_timezone_set('PRC');
            //获得基本的数据
            $cs_sql = new ClassStudentService();
            $stu_field = array("student_stu_id");
            $students = $cs_sql->unsetStu($cs_sql->getClassStuInfo(0, $stu_field, 1));     //获得所有学生
            $qs_sql = new QuestionSetService();
            $quiz = $qs_sql->getAllOpenQuiz();              //获得所有试卷
            $sr_sql = new StudentResultService();
            for ($i = 0; $i < count($students); $i++) {           //整理学生格式(加上学生成绩)
                $_POST["stu_id"] = $students[$i]["stu_id"];
                for ($j = 0; $j < count($quiz); $j++) {
                    $_POST["qus_id"] = $quiz[$j]["qus_id"];
                    $students[$i]["allResult"][$j] = $sr_sql->getStuQuizResult();
                }
            }
            //创建Excel表
            $objPHPExcel = new \PHPExcel();
            $objSheet = $objPHPExcel->getActiveSheet();     //获得活动sheet
            //设置默认样式都是居中+自动换行
            $objSheet->getDefaultStyle()->getAlignment()->setWrapText(true)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objSheet->setCellValue("A1", "学号")->setCellValue("B1", "姓名");      //标题
            $indexWord = "B";
            for ($i = 0; $i < count($quiz); $i++) {
                $indexWord = $this->getIndexWord($i + 2);     //获得标题试卷的下标字母 从C开始
                $time = strtotime($quiz[$i]["qus_create_time"]);
                $date = date("m-d", $time);
                $objSheet->setCellValue($indexWord . "1", $quiz[$i]["qus_name"] . "\n" . $date);       //标题
                $objSheet->getColumnDimension($indexWord)->setWidth(15);
            }
            $objSheet->getStyle("A1:" . $indexWord . "1")->getFont()->setBold(true)->setName("微软雅黑");        //标题加粗
            $objSheet->getStyle("A1:" . $indexWord . "1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
            $objSheet->getStyle("A1:" . $indexWord . "1")->getFill()->getStartColor()->setRGB("7EDAF3");        //标题填充颜色
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
//                        'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                        'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                        'color' => array('argb' => 'D7D7D7'),
                    ),
                ),
            );
            $objSheet->getStyle("A1:" . $indexWord . "1")->applyFromArray($styleArray);
            $objSheet->getColumnDimension('A')->setWidth(25);
            $objSheet->getColumnDimension('B')->setWidth(15);
            for ($i = 0; $i < count($students); $i++) {
                $objSheet->setCellValueExplicit("A" . ($i + 2), $students[$i]["stu_number"],\PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue("B" . ($i + 2), $students[$i]["stu_name"]);
                $objSheet->getStyle("A" . ($i + 2))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                for ($j = 0; $j < count($students[$i]["allResult"]); $j++) {
                    $indexWord = $this->getIndexWord($j + 2);     //获得成绩的下标字母  从C开始
                    $objSheet->setCellValue($indexWord . ($i + 2), $students[$i]["allResult"][$j]["stu_score"]);
                    if ($students[$i]["allResult"][$j]["stu_score"] == "未交") {
                        $objSheet->getStyle($indexWord . ($i + 2))->getFont()->getColor()->setRGB("B15159");        //未交的颜色
                    } else {
                        $objSheet->getStyle($indexWord . ($i + 2))->getFont()->getColor()->setRGB("3C763D");        //有成绩的颜色
                    }
                }
            }
            $objWrite = \PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel5");
            $this->export("学生成绩表".date("Y-m-d",time()));
            $objWrite->save("php://output");
        } else {
            $this->error($check->message);
        }
    }
    //---------------------------------导出班级学生的名单----------------------------------------------
    public function exportClassStudent(){
        vendor("PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $cla_sql = new ClassService();
        $check = $cla_sql->checkPower();
        if($check->status == 1) {
            //准备数据
            $cs_sql = new ClassStudentService();
            $stu_field = array("student_stu_id");
            $students = $cs_sql->getClassStuInfo(0, $stu_field, 1);     //获得所有学生
            $class = $cla_sql->classInfo();
            //创建excel
            $objPHPExcel = new \PHPExcel();
            $objSheet = $objPHPExcel->getActiveSheet();
            $objSheet->setCellValue("A1", "学号")->setCellValue("B1", "姓名")->setCellValue("C1", "邮箱");
            $objSheet->getDefaultStyle()->getAlignment()->setWrapText(true)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
//                        'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                        'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                        'color' => array('argb' => 'D7D7D7'),
                    ),
                ),
            );
            $objSheet->getStyle("A1:C1")->applyFromArray($styleArray);
            $objSheet->getStyle("A1:C1")->getFont()->setBold(true)->setName("微软雅黑");        //标题加粗
            $objSheet->getStyle("A1:C1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
            $objSheet->getStyle("A1:C1")->getFill()->getStartColor()->setRGB("7EDAF3");        //标题填充颜色
            for ($i = 0; $i < 3; $i++) {
                $objSheet->getColumnDimension($this->getIndexWord($i))->setWidth(25);
                $objSheet->getStyle($this->getIndexWord($i) . "1")->getFont()->setSize(12);
            }
            for ($i = 0; $i < count($students); $i++) {
                $objSheet->setCellValueExplicit("A" . ($i + 2), $students[$i]["stu_number"], \PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue("B" . ($i + 2), $students[$i]["stu_name"])->setCellValue("C" . ($i + 2), $students[$i]["stu_email"]);
                $objSheet->getStyle("A" . ($i + 2))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
            }
            //保存处理
            $objWrite = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
            $this->export($class["cla_name"] . "班级学生名单" . date("Y-m-d", time()));
            $objWrite->save("php://output");
        }
        else{
            $this->error($check->message);
        }
    }
    //---------------------------------导出某次测试的成绩----------------------------------------------
    public function  exportQuizGrade(){
        vendor("PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $_POST["qus_id"] = $_GET["qd"];
        $quiz_sql = new QuestionSetService();
        $check = $quiz_sql->checkPower();
        if($check->status == 1){
            $quiz_field = array("qus_name","class_cla_id");
            $quiz = $quiz_sql->getQuizInfo($quiz_field);            //获取试卷名称
            $_GET["cad"] = $quiz["class_cla_id"];
            $cla_sql = new ClassService();
            $class = $cla_sql->classInfo();                         //获取班级信息
            $cs_sql = new ClassStudentService();
            $stu_field = array("student_stu_id");
            $students = $cs_sql->getClassStuInfo(0, $stu_field, 1);     //获得所有学生
            $sr_sql = new StudentResultService();
            for ($i = 0; $i < count($students); $i++) {           //整理学生格式(加上学生成绩)
                $_POST["stu_id"] = $students[$i]["stu_id"];
                $students[$i]["quizResult"] = $sr_sql->getStuQuizResult();
                unset($students[$i]["student_stu_id"]);
                unset($students[$i]["stu_id"]);
                unset($students[$i]["stu_email"]);
            }
            //生成excel表格
            $objPHPExcel = new \PHPExcel();
            $objSheet = $objPHPExcel->getSheet();
            $objSheet->setCellValue("A1","学号")->setCellValue("B1","姓名")->setCellValue("C1","成绩");
            $objSheet->getDefaultStyle()->getAlignment()->setWrapText(true)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
//                        'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                        'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                        'color' => array('argb' => 'D7D7D7'),
                    ),
                ),
            );
            $objSheet->getStyle("A1:C1")->applyFromArray($styleArray);
            $objSheet->getStyle("A1:C1")->getFont()->setBold(true)->setName("微软雅黑");        //标题加粗
            $objSheet->getStyle("A1:C1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
            $objSheet->getStyle("A1:C1")->getFill()->getStartColor()->setRGB("7EDAF3");        //标题填充颜色
            $objSheet->getColumnDimension('A')->setWidth(25);
            $objSheet->getColumnDimension('B')->setWidth(15);
            for ($i = 0; $i < count($students); $i++) {
                $objSheet->setCellValueExplicit("A" . ($i + 2), $students[$i]["stu_number"], \PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue("B" . ($i + 2), $students[$i]["stu_name"])->setCellValue("C" . ($i + 2), $students[$i]["quizResult"]["stu_score"]);
                $objSheet->getStyle("A" . ($i + 2))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
            }
            //保存
            $objWrite = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
            $this->export($class["cla_name"] . "_".$quiz["qus_name"]."_成绩表".date("Y-m-d", time()));
            $objWrite->save("php://output");
        }
        else{
            $this->error($check->message);
        }
    }
    //---------------------------------导入txt文件----------------------------------------------有待修改..还未完善
    public function importTxt(){
        if($_FILES["file"]["type"] == "text/plain") {       //判断文件类型
            $_POST["class_cla_id"] = $_GET["cad"];
            $cla_stu_sql = new ClassStudentService();
            $check = $cla_stu_sql->checkPower();
            if($check->status == 1) {           //检测权限
                $storage = new \SaeStorage();           //开启storage
                $storage->upload("excel","/excel/".$_FILES["file"]["name"],$_FILES["file"]["tmp_name"]);        //上传文件
                $file = $storage->getUrl("excel","/excel/".$_FILES["file"]["name"]);        //获得文件url
                $content = iconv("GBK", "UTF-8",file_get_contents($file));          //获得内容转成GBK
                $array = explode("\r\n",$content);              //变成学号数组
                $tea_sql = new TeacherService();
                $tea_field = array("college_col_id");
                $teacher = $tea_sql->getInfo($tea_field);
                $_POST["col_id"] = $teacher["college_col_id"];          //获得学校id...查同个学校的
                $stu_sql = new StudentService();
                $f = 0;
                $m = 0;
                $stu_field = array("stu_id","stu_number","stu_name");
                for($i = 0;$i<count($array);$i++){              //获取学生基本信息
                    if($array[$i]!="") {
                        $_POST["stu_number"] = $array[$i];
                        $student = $stu_sql->getOneStuInfo($stu_field);
                        if ($student->status == 1) {
                            $stu["find"][$f] = $student->data;             //成功的人员的名单
                            $stu_id[$f++] = $student->data["stu_id"];       //获取id,即将导入的id
                        } else {
                            $stu["miss"][$m++] = $array[$i];            //找不到的学号
                        }
                    }
                }
                unset($_POST);          //清空post,重新整理
                $_POST["class_cla_id"] = $_GET["cad"];
                $_POST["stu_id"] = $stu_id;
                $data = $cla_stu_sql->importStu();      //导入学生
                $d["success"] = $stu["find"];
                $d["miss"] = $stu["miss"];
                $data->data = $d;
                $storage->delete("excel","/excel/".$_FILES["file"]["name"]);    //删除上传文件
            }
            else{
                $data = $check;
            }
        }
        else{
            $data = new Message(0,"文件类型错误,请上传txt文本文件!","");
        }
        dump($data);
    }
    private function export($filename){         //2007版本以上...下载
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$filename.'.xls"');
        header('Content-Disposition: attachment;filename="'.iconv("UTF-8", "GBK", $filename).'.xls"');
        header('Cache-Control: max-age=0');
    }
    private function getIndexWord($index){      //获得下标字母...导出
        $array = range("A","Z");
        return $array[$index];
    }
}